create table jms_dm.dm_mng_explosion_warehouse_network_second_dt
(
    final_sign_network_code varchar(100) comment '最终签收派件网点编码',
    end_agent_code          varchar(100) comment '末端网点所属代理区编码',
    final_sign_network_name varchar(100) comment '最终签收派件网点编码',
    end_agent_name          varchar(100) comment '末端网点所属代理区名称',
    need_sign_count_1d      int comment '1天应签收',
    retention_count_1d      int comment '1天内滞留',
    need_sign_count_2d      int comment '2天应签收',
    retention_count_2d      int comment '2天内滞留',
    need_sign_count_3d      int comment '3天应签收',
    retention_count_3d      int comment '3天内滞留',
    need_sign_count_5d      int comment '5天应签收',
    retention_count_5d      int comment '5天内滞留',
    need_sign_count_7d      int comment '7天应签收',
    retention_count_7d      int comment '7天内滞留',
    scan_sum                int comment '时效签收量',
    staff_code              varchar(100) comment '员工编码',
    staff_name              varchar(100) comment '员工名称',
    position_name           varchar(100) comment '岗位名称',
    is_warn                 int comment '是否预警,全为1',
    final_plan_sign_date    date comment '规划签收日期',
    agent_type              varchar(100) comment '类型,虚拟代理区,代理区',
    virt_code              varchar(100) comment '虚拟代理区,代理区',
    virt_name              varchar(100) comment '虚拟代理区,代理区',
    end_provider_id              varchar(100) comment '网点所属省id',
    end_provider_name              varchar(100) comment '网点所属省name'
) ENGINE = OLAP DUPLICATE KEY(final_sign_network_code,end_agent_code)
COMMENT '网点滞留预警汇总新'
PARTITION BY RANGE(final_plan_sign_date)
(START ('2023-02-01') END ('2023-03-10') EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(final_plan_sign_date) BUCKETS 1
PROPERTIES (
'replication_num' = '3',
'dynamic_partition.enable' = 'true',
'dynamic_partition.time_unit' = 'DAY',
'dynamic_partition.time_zone' = 'Asia/Shanghai',
'dynamic_partition.start' = '-180',
'dynamic_partition.end' = '3',
'dynamic_partition.prefix' = 'p',
'dynamic_partition.buckets' = '1',
'in_memory' = 'false',
'storage_format' = 'V2'
);